
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 01/03/2015 14:29:01
-- Generated from EDMX file: E:\Work\LanguagePolish\LanguageSchool\EntityModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [LanguageSchool];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Users_AccountTypes]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Users] DROP CONSTRAINT [FK_Users_AccountTypes];
GO
IF OBJECT_ID(N'[dbo].[FK_Classes_Classrooms]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Classes] DROP CONSTRAINT [FK_Classes_Classrooms];
GO
IF OBJECT_ID(N'[dbo].[FK_Classes_Levels]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Classes] DROP CONSTRAINT [FK_Classes_Levels];
GO
IF OBJECT_ID(N'[dbo].[FK_Classes_Teachers]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Classes] DROP CONSTRAINT [FK_Classes_Teachers];
GO
IF OBJECT_ID(N'[dbo].[FK_Shedule_Classes]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Shedules] DROP CONSTRAINT [FK_Shedule_Classes];
GO
IF OBJECT_ID(N'[dbo].[FK_Students_Classes]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Students] DROP CONSTRAINT [FK_Students_Classes];
GO
IF OBJECT_ID(N'[dbo].[FK_Shedule_Classrooms1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Shedules] DROP CONSTRAINT [FK_Shedule_Classrooms1];
GO
IF OBJECT_ID(N'[dbo].[FK_Shedule_Subjects]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Shedules] DROP CONSTRAINT [FK_Shedule_Subjects];
GO
IF OBJECT_ID(N'[dbo].[FK_Shedule_Teachers]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Shedules] DROP CONSTRAINT [FK_Shedule_Teachers];
GO
IF OBJECT_ID(N'[dbo].[FK_Shedule_Users]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Shedules] DROP CONSTRAINT [FK_Shedule_Users];
GO
IF OBJECT_ID(N'[dbo].[FK_Shedule_Users1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Shedules] DROP CONSTRAINT [FK_Shedule_Users1];
GO
IF OBJECT_ID(N'[dbo].[FK_TeacherAvailability_Teachers]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TeacherAvailabilities] DROP CONSTRAINT [FK_TeacherAvailability_Teachers];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[AccountTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AccountTypes];
GO
IF OBJECT_ID(N'[dbo].[Classes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Classes];
GO
IF OBJECT_ID(N'[dbo].[Classrooms]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Classrooms];
GO
IF OBJECT_ID(N'[dbo].[Levels]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Levels];
GO
IF OBJECT_ID(N'[dbo].[Shedules]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Shedules];
GO
IF OBJECT_ID(N'[dbo].[Students]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Students];
GO
IF OBJECT_ID(N'[dbo].[Subjects]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Subjects];
GO
IF OBJECT_ID(N'[dbo].[Teachers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Teachers];
GO
IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO
IF OBJECT_ID(N'[dbo].[TeacherAvailabilities]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TeacherAvailabilities];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'AccountTypes'
CREATE TABLE [dbo].[AccountTypes] (
    [AccountTypeID] int IDENTITY(1,1) NOT NULL,
    [AccountTypeName] nvarchar(50)  NULL,
    [Permissions] nvarchar(500)  NULL
);
GO

-- Creating table 'Classes'
CREATE TABLE [dbo].[Classes] (
    [ClassID] int IDENTITY(1,1) NOT NULL,
    [ClassName] nvarchar(100)  NULL,
    [ClassroomID] int  NULL,
    [TeacherID] int  NULL,
    [Capacity] int  NULL,
    [LevelID] int  NULL,
    [Duration] int  NULL,
    [HoursPerDay] decimal(10,2)  NULL,
    [StartDate] datetime  NULL,
    [EndDate] datetime  NULL
);
GO

-- Creating table 'Classrooms'
CREATE TABLE [dbo].[Classrooms] (
    [ClassroomID] int IDENTITY(1,1) NOT NULL,
    [Info] nvarchar(1000)  NULL
);
GO

-- Creating table 'Levels'
CREATE TABLE [dbo].[Levels] (
    [LevelID] int IDENTITY(1,1) NOT NULL,
    [LevelName] nvarchar(500)  NULL
);
GO

-- Creating table 'Shedules'
CREATE TABLE [dbo].[Shedules] (
    [SheduleID] int IDENTITY(1,1) NOT NULL,
    [SubjectID] int  NULL,
    [TeacherID] int  NULL,
    [ClassID] int  NULL,
    [ClassroomID] int  NULL,
    [LessonDate] datetime  NULL,
    [TimeStart] datetime  NULL,
    [TimeEnd] datetime  NULL,
    [Creator] int  NULL,
    [Editor] int  NULL
);
GO

-- Creating table 'Students'
CREATE TABLE [dbo].[Students] (
    [StudentID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(500)  NULL,
    [Email] nvarchar(500)  NULL,
    [Phone] nvarchar(100)  NULL,
    [Nationality] nvarchar(100)  NULL,
    [ClassID] int  NULL,
    [TotalPaid] decimal(10,2)  NULL,
    [Gender] bit  NULL,
    [AdresRes] nvarchar(500)  NULL,
    [AdresWarshaw] nvarchar(500)  NULL,
    [IsArchieved] bit  NOT NULL,
    [NeedResidencePermit] bit  NOT NULL,
    [Notes] nvarchar(1000)  NULL
);
GO

-- Creating table 'Subjects'
CREATE TABLE [dbo].[Subjects] (
    [SubjectID] int IDENTITY(1,1) NOT NULL,
    [SubjectName] nvarchar(100)  NULL
);
GO

-- Creating table 'Teachers'
CREATE TABLE [dbo].[Teachers] (
    [TeacherID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(500)  NULL,
    [Email] nvarchar(500)  NULL,
    [Phone] nvarchar(100)  NULL,
    [HourlyRate] decimal(10,2)  NULL,
    [Adres] nvarchar(500)  NULL,
    [BankName] nvarchar(500)  NULL,
    [AccountNumber] nvarchar(100)  NULL,
    [Etc] nvarchar(500)  NULL,
    [Photo] nvarchar(500)  NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [UserID] int IDENTITY(1,1) NOT NULL,
    [FirstName] nvarchar(500)  NULL,
    [LastName] nvarchar(500)  NULL,
    [UserLogin] nvarchar(100)  NULL,
    [UserPassword] nvarchar(100)  NULL,
    [AccountType] int  NULL
);
GO

-- Creating table 'TeacherAvailabilities'
CREATE TABLE [dbo].[TeacherAvailabilities] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [TeacherID] int  NOT NULL,
    [WeekDay] int  NOT NULL,
    [TimeFrom1] time  NULL,
    [TimeTo1] time  NULL,
    [TimeFrom2] time  NULL,
    [TimeTo2] time  NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [AccountTypeID] in table 'AccountTypes'
ALTER TABLE [dbo].[AccountTypes]
ADD CONSTRAINT [PK_AccountTypes]
    PRIMARY KEY CLUSTERED ([AccountTypeID] ASC);
GO

-- Creating primary key on [ClassID] in table 'Classes'
ALTER TABLE [dbo].[Classes]
ADD CONSTRAINT [PK_Classes]
    PRIMARY KEY CLUSTERED ([ClassID] ASC);
GO

-- Creating primary key on [ClassroomID] in table 'Classrooms'
ALTER TABLE [dbo].[Classrooms]
ADD CONSTRAINT [PK_Classrooms]
    PRIMARY KEY CLUSTERED ([ClassroomID] ASC);
GO

-- Creating primary key on [LevelID] in table 'Levels'
ALTER TABLE [dbo].[Levels]
ADD CONSTRAINT [PK_Levels]
    PRIMARY KEY CLUSTERED ([LevelID] ASC);
GO

-- Creating primary key on [SheduleID] in table 'Shedules'
ALTER TABLE [dbo].[Shedules]
ADD CONSTRAINT [PK_Shedules]
    PRIMARY KEY CLUSTERED ([SheduleID] ASC);
GO

-- Creating primary key on [StudentID] in table 'Students'
ALTER TABLE [dbo].[Students]
ADD CONSTRAINT [PK_Students]
    PRIMARY KEY CLUSTERED ([StudentID] ASC);
GO

-- Creating primary key on [SubjectID] in table 'Subjects'
ALTER TABLE [dbo].[Subjects]
ADD CONSTRAINT [PK_Subjects]
    PRIMARY KEY CLUSTERED ([SubjectID] ASC);
GO

-- Creating primary key on [TeacherID] in table 'Teachers'
ALTER TABLE [dbo].[Teachers]
ADD CONSTRAINT [PK_Teachers]
    PRIMARY KEY CLUSTERED ([TeacherID] ASC);
GO

-- Creating primary key on [UserID] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([UserID] ASC);
GO

-- Creating primary key on [Id] in table 'TeacherAvailabilities'
ALTER TABLE [dbo].[TeacherAvailabilities]
ADD CONSTRAINT [PK_TeacherAvailabilities]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [AccountType] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [FK_Users_AccountTypes]
    FOREIGN KEY ([AccountType])
    REFERENCES [dbo].[AccountTypes]
        ([AccountTypeID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Users_AccountTypes'
CREATE INDEX [IX_FK_Users_AccountTypes]
ON [dbo].[Users]
    ([AccountType]);
GO

-- Creating foreign key on [ClassroomID] in table 'Classes'
ALTER TABLE [dbo].[Classes]
ADD CONSTRAINT [FK_Classes_Classrooms]
    FOREIGN KEY ([ClassroomID])
    REFERENCES [dbo].[Classrooms]
        ([ClassroomID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Classes_Classrooms'
CREATE INDEX [IX_FK_Classes_Classrooms]
ON [dbo].[Classes]
    ([ClassroomID]);
GO

-- Creating foreign key on [LevelID] in table 'Classes'
ALTER TABLE [dbo].[Classes]
ADD CONSTRAINT [FK_Classes_Levels]
    FOREIGN KEY ([LevelID])
    REFERENCES [dbo].[Levels]
        ([LevelID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Classes_Levels'
CREATE INDEX [IX_FK_Classes_Levels]
ON [dbo].[Classes]
    ([LevelID]);
GO

-- Creating foreign key on [TeacherID] in table 'Classes'
ALTER TABLE [dbo].[Classes]
ADD CONSTRAINT [FK_Classes_Teachers]
    FOREIGN KEY ([TeacherID])
    REFERENCES [dbo].[Teachers]
        ([TeacherID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Classes_Teachers'
CREATE INDEX [IX_FK_Classes_Teachers]
ON [dbo].[Classes]
    ([TeacherID]);
GO

-- Creating foreign key on [ClassID] in table 'Shedules'
ALTER TABLE [dbo].[Shedules]
ADD CONSTRAINT [FK_Shedule_Classes]
    FOREIGN KEY ([ClassID])
    REFERENCES [dbo].[Classes]
        ([ClassID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Shedule_Classes'
CREATE INDEX [IX_FK_Shedule_Classes]
ON [dbo].[Shedules]
    ([ClassID]);
GO

-- Creating foreign key on [ClassID] in table 'Students'
ALTER TABLE [dbo].[Students]
ADD CONSTRAINT [FK_Students_Classes]
    FOREIGN KEY ([ClassID])
    REFERENCES [dbo].[Classes]
        ([ClassID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Students_Classes'
CREATE INDEX [IX_FK_Students_Classes]
ON [dbo].[Students]
    ([ClassID]);
GO

-- Creating foreign key on [ClassroomID] in table 'Shedules'
ALTER TABLE [dbo].[Shedules]
ADD CONSTRAINT [FK_Shedule_Classrooms1]
    FOREIGN KEY ([ClassroomID])
    REFERENCES [dbo].[Classrooms]
        ([ClassroomID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Shedule_Classrooms1'
CREATE INDEX [IX_FK_Shedule_Classrooms1]
ON [dbo].[Shedules]
    ([ClassroomID]);
GO

-- Creating foreign key on [SubjectID] in table 'Shedules'
ALTER TABLE [dbo].[Shedules]
ADD CONSTRAINT [FK_Shedule_Subjects]
    FOREIGN KEY ([SubjectID])
    REFERENCES [dbo].[Subjects]
        ([SubjectID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Shedule_Subjects'
CREATE INDEX [IX_FK_Shedule_Subjects]
ON [dbo].[Shedules]
    ([SubjectID]);
GO

-- Creating foreign key on [TeacherID] in table 'Shedules'
ALTER TABLE [dbo].[Shedules]
ADD CONSTRAINT [FK_Shedule_Teachers]
    FOREIGN KEY ([TeacherID])
    REFERENCES [dbo].[Teachers]
        ([TeacherID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Shedule_Teachers'
CREATE INDEX [IX_FK_Shedule_Teachers]
ON [dbo].[Shedules]
    ([TeacherID]);
GO

-- Creating foreign key on [Creator] in table 'Shedules'
ALTER TABLE [dbo].[Shedules]
ADD CONSTRAINT [FK_Shedule_Users]
    FOREIGN KEY ([Creator])
    REFERENCES [dbo].[Users]
        ([UserID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Shedule_Users'
CREATE INDEX [IX_FK_Shedule_Users]
ON [dbo].[Shedules]
    ([Creator]);
GO

-- Creating foreign key on [Editor] in table 'Shedules'
ALTER TABLE [dbo].[Shedules]
ADD CONSTRAINT [FK_Shedule_Users1]
    FOREIGN KEY ([Editor])
    REFERENCES [dbo].[Users]
        ([UserID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Shedule_Users1'
CREATE INDEX [IX_FK_Shedule_Users1]
ON [dbo].[Shedules]
    ([Editor]);
GO

-- Creating foreign key on [TeacherID] in table 'TeacherAvailabilities'
ALTER TABLE [dbo].[TeacherAvailabilities]
ADD CONSTRAINT [FK_TeacherAvailability_Teachers]
    FOREIGN KEY ([TeacherID])
    REFERENCES [dbo].[Teachers]
        ([TeacherID])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeacherAvailability_Teachers'
CREATE INDEX [IX_FK_TeacherAvailability_Teachers]
ON [dbo].[TeacherAvailabilities]
    ([TeacherID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------